﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QLVatTu.Class;
using QLVatTu.InBaoCao;

namespace QLVatTu.BaoCao
{
    public partial class frmBaoCaoNhapXuatTon : Form
    {
        KetNoiDB cn = new KetNoiDB();

        public frmBaoCaoNhapXuatTon()
        {
            InitializeComponent();
        }

        private void loadData()
        {
            cmbKhoLoc.DataSource = cn.getTable("SELECT [MaKho],[TenKho] FROM [tbl_Kho]");
            cmbKhoLoc.DisplayMember = "TenKho";
            cmbKhoLoc.ValueMember = "MaKho";

            load_grid_view();
        }

        private void load_grid_view(string dieuKien = null)
        {
            string sql = "";
            if (dieuKien != null)
            {
                sql = @"
                    SELECT     dbo.tbl_VatTu.MaVatTu, dbo.tbl_VatTu.TenVatTu, dbo.tbl_VatTu.MaDonViTinh
                    FROM         dbo.tbl_LuuKho INNER JOIN
                                          dbo.tbl_VatTu ON dbo.tbl_LuuKho.MaVatTu = dbo.tbl_VatTu.MaVatTu
                    " + dieuKien + @"
                    GROUP BY dbo.tbl_VatTu.MaVatTu, dbo.tbl_VatTu.TenVatTu, dbo.tbl_VatTu.MaDonViTinh
                    ORDER BY dbo.tbl_VatTu.MaVatTu ASC";
            }
            else
            {
                sql = @"
                    SELECT     dbo.tbl_VatTu.MaVatTu, dbo.tbl_VatTu.TenVatTu, dbo.tbl_VatTu.MaDonViTinh
                    FROM         dbo.tbl_LuuKho INNER JOIN
                                          dbo.tbl_VatTu ON dbo.tbl_LuuKho.MaVatTu = dbo.tbl_VatTu.MaVatTu
                    GROUP BY dbo.tbl_VatTu.MaVatTu, dbo.tbl_VatTu.TenVatTu, dbo.tbl_VatTu.MaDonViTinh
                    ORDER BY dbo.tbl_VatTu.MaVatTu ASC
                ";
            }
            dgvNXT.Rows.Clear();
            DataTable dtVatTu = cn.getTable(sql);
            if (dtVatTu.Rows.Count > 0)
            {

                int stt = 0;
                string dkTonDauKi;
                string _MaVatTu;
                string _VatTu;
                string _DVT;
                float _TonDauKy;
                float _Nhap;
                float _Xuat;
                float _Ton;
                foreach (DataRow dr in dtVatTu.Rows)
                {
                    stt++;
                    _MaVatTu = "";
                    _VatTu = "";
                    _DVT = "";
                    _TonDauKy = 0;
                    _Nhap = 0;
                    _Xuat = 0;
                    _Ton = 0;

                    _MaVatTu = dr["MaVatTu"].ToString();
                    _VatTu = dr["TenVatTu"].ToString();
                    _DVT = cn.LayDuLieu("SELECT [TenDonViTinh] FROM [tbl_DonViTinh] WHERE MaDonViTinh = '" + dr["MaDonViTinh"].ToString() + "'");

                    if (dieuKien != null)
                    {
                        sql = "SELECT SUM(SoLuong) as SoLuong FROM [tbl_LuuKho] WHERE MaVatTu = '" + dr["MaVatTu"].ToString() + "' ";
                        dkTonDauKi = "";
                        // Dieu kien loc ngay thang
                        if (rbtnLocThang.Checked == true && cmbThang.Text != "")
                        {
                            dkTonDauKi += " AND dbo.tbl_LuuKho.NgayCapNhat < '" + nudNamThang.Value.ToString() + "-" + cmbThang.Text + "-01'";
                            DataTable dtTonDauKi = cn.getTable(sql + dkTonDauKi);
                            if (dtTonDauKi.Rows.Count > 0)
                            {
                                try
                                {
                                    _TonDauKy = float.Parse(dtTonDauKi.Rows[0]["SoLuong"].ToString());
                                }
                                catch { }
                            }
                        }
                        else if (rbtnLocNgay.Checked == true)
                        {
                            dkTonDauKi += " AND dbo.tbl_LuuKho.NgayCapNhat < '" + dtipTuNgay.Value.ToString("yyyy-MM-dd") + "'";
                            DataTable dtTonDauKi = cn.getTable(sql + dkTonDauKi);
                            if (dtTonDauKi.Rows.Count > 0)
                            {
                                try
                                {
                                    _TonDauKy = float.Parse(dtTonDauKi.Rows[0]["SoLuong"].ToString());
                                }
                                catch { }
                            }
                        }
                        else if (rbtnTheoNgay.Checked == true)
                        {
                            dkTonDauKi += " AND dbo.tbl_LuuKho.NgayCapNhat < '" + dtipTheoNgay.Value.Year + "-" + dtipTheoNgay.Value.Month + "-" + dtipTheoNgay.Value.Day + "'";
                            DataTable dtTonDauKi = cn.getTable(sql + dkTonDauKi);
                            if (dtTonDauKi.Rows.Count > 0)
                            {
                                try
                                {
                                    _TonDauKy = float.Parse(dtTonDauKi.Rows[0]["SoLuong"].ToString());
                                }
                                catch { }
                            }
                        }

                        sql = "SELECT SUM(SoLuong) as SoLuong FROM [tbl_LuuKho] " + dieuKien + " AND MaVatTu = '" + dr["MaVatTu"].ToString() + "' ";
                        DataTable dtNhap = cn.getTable(sql + " AND SoLuong > 0");
                        if (dtNhap.Rows.Count > 0)
                        {
                            try
                            {
                                _Nhap = float.Parse(dtNhap.Rows[0]["SoLuong"].ToString());
                            }
                            catch { }
                        }

                        DataTable dtXuat = cn.getTable(sql + " AND SoLuong < 0");
                        if (dtXuat.Rows.Count > 0)
                        {
                            try
                            {
                                _Xuat = -float.Parse(dtXuat.Rows[0]["SoLuong"].ToString());
                            }
                            catch { }
                        }

                        DataTable dtTon = cn.getTable(sql);
                        if (dtTon.Rows.Count > 0)
                        {
                            try
                            {
                                _Ton = float.Parse(dtTon.Rows[0]["SoLuong"].ToString()) + _TonDauKy;
                            }
                            catch { }
                        }
                    }
                    else
                    {
                        sql = "SELECT SUM(SoLuong) as SoLuong FROM [tbl_LuuKho] WHERE MaVatTu = '" + dr["MaVatTu"].ToString() + "' ";
                        DataTable dtNhap = cn.getTable(sql + " AND SoLuong > 0");
                        if (dtNhap.Rows.Count > 0)
                        {
                            try
                            {
                                _Nhap = float.Parse(dtNhap.Rows[0]["SoLuong"].ToString());
                            }
                            catch { }
                        }

                        DataTable dtXuat = cn.getTable(sql + " AND SoLuong < 0");
                        if (dtXuat.Rows.Count > 0)
                        {
                            try
                            {
                                _Xuat = -float.Parse(dtXuat.Rows[0]["SoLuong"].ToString());
                            }
                            catch { }
                        }

                        DataTable dtTon = cn.getTable(sql);
                        if (dtTon.Rows.Count > 0)
                        {
                            try
                            {
                                _Ton = float.Parse(dtTon.Rows[0]["SoLuong"].ToString()) + _TonDauKy;
                            }
                            catch { }
                        }
                    }

                    DataGridViewRow dgvr = (DataGridViewRow)dgvNXT.Rows[0].Clone();
                    dgvr.Cells[0].Value = stt;
                    dgvr.Cells[1].Value = _MaVatTu;
                    dgvr.Cells[2].Value = _VatTu;
                    dgvr.Cells[3].Value = _DVT;
                    dgvr.Cells[4].Value = _TonDauKy;
                    dgvr.Cells[5].Value = _Nhap;
                    dgvr.Cells[6].Value = _Xuat;
                    dgvr.Cells[7].Value = _Ton;
                    dgvNXT.Rows.Add(dgvr);
                }
            }
        }

        private void frmBaoCaoNhapXuatTon_Load(object sender, EventArgs e)
        {
            if (cn.KetNoi())
            {
                loadData();
            }
            else
            {
                MessageBox.Show("Kết nối CSDL thất bại!");
                this.Close();
            }
        }
        private void ckbKhoNhap_CheckedChanged(object sender, EventArgs e)
        {
            if (ckbKhoLoc.Checked == true)
            {
                cmbKhoLoc.Enabled = true;
            }
            else
            {
                cmbKhoLoc.Enabled = false;
            }
        }

        private void rbtnLocThang_CheckedChanged(object sender, EventArgs e)
        {
            if (rbtnLocThang.Checked == true)
            {
                cmbThang.Enabled = true;
                nudNamThang.Enabled = true;
            }
            else
            {
                cmbThang.Enabled = false;
                nudNamThang.Enabled = false;
            }
        }

        private void rbtnLocNgay_CheckedChanged(object sender, EventArgs e)
        {
            if (rbtnLocNgay.Checked == true)
            {
                dtipTuNgay.Enabled = true;
                dtipDenNgay.Enabled = true;
            }
            else
            {
                dtipTuNgay.Enabled = false;
                dtipDenNgay.Enabled = false;
            }
        }

        private void rbtnTheoNgay_CheckedChanged(object sender, EventArgs e)
        {
            if (rbtnTheoNgay.Checked == true)
            {
                dtipTheoNgay.Enabled = true;
            }
            else
            {
                dtipTheoNgay.Enabled = false;
            }
        }

        private string XacDinhDieuKienLoc()
        {
            string dieuKienLoc = " WHERE 1=1 ";
            if (ckbKhoLoc.Checked == true && cmbKhoLoc.Items.Count > 0)
            {
                dieuKienLoc += " AND dbo.tbl_LuuKho.MaKho = '" + cmbKhoLoc.SelectedValue.ToString() + "'";
            }

            // Dieu kien loc ngay thang
            if (rbtnLocThang.Checked == true && cmbThang.Text != "")
            {
                dieuKienLoc += " AND MONTH(dbo.tbl_LuuKho.NgayCapNhat) = " + cmbThang.Text + " AND YEAR(dbo.tbl_LuuKho.NgayCapNhat) = " + nudNamThang.Value.ToString();
            }
            else if (rbtnLocNgay.Checked == true)
            {
                dieuKienLoc += " AND dbo.tbl_LuuKho.NgayCapNhat BETWEEN '" + dtipTuNgay.Value.ToString("yyyy-MM-dd") + "' AND '" + dtipDenNgay.Value.ToString("yyyy-MM-dd") + "'";
            }
            else if (rbtnTheoNgay.Checked == true)
            {
                dieuKienLoc += " AND DAY(dbo.tbl_LuuKho.NgayCapNhat) = " + dtipTheoNgay.Value.Day + " AND MONTH(dbo.tbl_LuuKho.NgayCapNhat) = " + dtipTheoNgay.Value.Month + " AND YEAR(dbo.tbl_LuuKho.NgayCapNhat) = " + dtipTheoNgay.Value.Year;
            }
            return dieuKienLoc;
        }

        private void btnLoc_Click(object sender, EventArgs e)
        {
            load_grid_view(XacDinhDieuKienLoc());
        }

        private void btnIn_Click(object sender, EventArgs e)
        {
            frmInBaoCaoNhapXuatTon frm = new frmInBaoCaoNhapXuatTon();
            List<BaoCaoNhapXuatTon> list = new List<BaoCaoNhapXuatTon>();
            foreach (DataGridViewRow row in dgvNXT.Rows)
            {
                if (row.IsNewRow) continue;
                BaoCaoNhapXuatTon _BaoCao = new BaoCaoNhapXuatTon();
                _BaoCao.STT = row.Cells["STT"].Value.ToString();
                _BaoCao.MaVatTu = row.Cells["MaVatTu"].Value.ToString();
                _BaoCao.VatTu = row.Cells["VatTu"].Value.ToString();
                _BaoCao.DVT = row.Cells["DVT"].Value.ToString();
                _BaoCao.DauKy = row.Cells["DauKy"].Value.ToString();
                _BaoCao.Nhap = row.Cells["Nhap"].Value.ToString();
                _BaoCao.Xuat = row.Cells["Xuat"].Value.ToString();
                _BaoCao.Ton = row.Cells["Ton"].Value.ToString();
                list.Add(_BaoCao);
            }
            frm.BaoCaoNhapXuatTonBindingSource.DataSource = list;
            frm.ShowDialog();
        }

    }
}
